# -*- coding: utf-8 -*-
"""
合并 CIER、毕业人数(万人)、GDP、三次产业占比（按时间对齐）
输出：aligned_quarterly_data.csv
"""

"""
把 CIER（季度）、GDP+三次产业占比（季度）与 毕业生人数（年频）对齐到【季度】。
毕业生人数会自动在该年每个季度填入同一数值（例如 2016 年的每个季度都用 2016 年的毕业生人数）。
输出：aligned_quarterly_data.csv
"""

import os
import numpy as np
import pandas as pd

# ========= 路径配置（按需修改） =========
PATH_CIER = "/Users/linshangjin/25CCM/NKU-C/dataset_csv/2016-2025 CIER.csv"
PATH_GDP  = "/Users/linshangjin/25CCM/NKU-C/dataset_csv/GDP_季度数据.csv"
PATH_GRAD = "/Users/linshangjin/25CCM/NKU-C/dataset_csv/graduates_2016_2025.csv"  # 年度毕业生人数（单位：万人，或写明单位）
OUT_PATH  = "/Users/linshangjin/25CCM/NKU-C/dataset_csv/aligned_quarterly_data2.csv"
# =====================================

def read_csv_flexible(path):
    try:
        return pd.read_csv(path, encoding="utf-8")
    except UnicodeDecodeError:
        return pd.read_csv(path, encoding="gbk")

def detect_date_col(cols):
    cands = ["date","时间","季度","period","Year","year","年份","Unnamed: 0"]
    for c in cands:
        if c in cols:
            return c
    # 兜底：任何包含 date/time/quarter 的列名
    for c in cols:
        lc = str(c).lower()
        if any(k in lc for k in ["date","time","quarter","period","year"]):
            return c
    return None

def to_quarter_end_index(s):
    """将任意日期索引序列标准化为季度末索引（取每季最后一个观测）"""
    s = s.dropna()
    s_q = s.resample("Q").last()
    s_q.index.freq = None  # 解除频率锁
    return s_q

# ---- 读 CIER（季频或更高频） ----
def load_cier_quarterly(path):
    df = read_csv_flexible(path)
    dcol = detect_date_col(df.columns)
    if dcol is None:
        raise ValueError("CIER 文件找不到日期列")
    df[dcol] = pd.to_datetime(df[dcol])
    # 找值列：优先 'CIER/CIER指数/value/指数'
    val = None
    for c in ["CIER","CIER指数","value","指数"]:
        if c in df.columns:
            val = c; break
    if val is None:
        # 取第一个非日期且数值的列
        for c in df.columns:
            if c != dcol and pd.api.types.is_numeric_dtype(df[c]):
                val = c; break
    if val is None:
        raise ValueError("CIER 文件未找到数值列")
    s = df[[dcol,val]].dropna().sort_values(dcol).set_index(dcol)[val].astype(float)
    return to_quarter_end_index(s).rename("CIER")

# ---- 读 GDP & 三次产业占比（季频或更高频）----
def load_gdp_quarterly(path):
    df = read_csv_flexible(path)
    dcol = detect_date_col(df.columns)
    if dcol is None:
        raise ValueError("GDP 文件找不到日期列")
    df[dcol] = pd.to_datetime(df[dcol])
    # 将非日期列转数值
    for c in df.columns:
        if c != dcol:
            df[c] = pd.to_numeric(df[c], errors="coerce")
    g = df.set_index(dcol).sort_index()
    g_q = g.resample("Q").last()
    # 常见列名清洗（可按你实际文件增删）
    rename_map = {
        "第一产业占比":"第一产业占比",
        "第二产业占比":"第二产业占比",
        "第三产业占比":"第三产业占比",
        "GDP":"GDP"
    }
    g_q = g_q.rename(columns=rename_map)
    return g_q

# ---- 读毕业生（年频），填充到每个季度 ----
def load_graduates_annual_to_quarter(path, q_index_union):
    df = read_csv_flexible(path)
    dcol = detect_date_col(df.columns)
    if dcol is None:
        raise ValueError("毕业生文件找不到日期/年份列")
    # 年识别
    if dcol in ["year","Year","年份"]:
        years = pd.to_numeric(df[dcol], errors="coerce").dropna().astype(int).values
        grad_col = None
        # 可能的列名
        for c in ["毕业生人数(万人)","毕业人数(万人)","毕业生人数","graduates_10k","Graduates_10k","graduates","Graduates"]:
            if c in df.columns:
                grad_col = c; break
        if grad_col is None:
            # 取第一个数值列作为毕业生列
            for c in df.columns:
                if c != dcol and pd.api.types.is_numeric_dtype(df[c]):
                    grad_col = c; break
        vals = pd.to_numeric(df[grad_col], errors="coerce").values
        ser_annual = pd.Series(vals, index=years, name="毕业生人数_万人").dropna()
    else:
        # 有 date，就转成年末并取 last
        df[dcol] = pd.to_datetime(df[dcol])
        for c in df.columns:
            if c != dcol:
                df[c] = pd.to_numeric(df[c], errors="coerce")
        A = df.set_index(dcol).sort_index().resample("A-DEC").last()
        ser_annual = A.iloc[:,0]  # 取第一列为毕业生人数
        ser_annual.index = ser_annual.index.year
        ser_annual.name = "毕业生人数_万人"

    # 将年值映射到每个季度：同一年所有季度取同一数值
    qidx = pd.Index(q_index_union)
    grads_q = pd.Series([ser_annual.get(ts.year, np.nan) for ts in qidx], index=qidx, name="毕业生人数_万人")
    return grads_q

# ===== 执行合并 =====
cier_q  = load_cier_quarterly(PATH_CIER)
gdp_q   = load_gdp_quarterly(PATH_GDP)

# 联合季度索引（以所有来源的并集为准）
q_union = cier_q.index.union(gdp_q.index)
# q_union = cier_q.index.intersection(gdp_q.index)

# 毕业生人数（按年→填入每个季度）
grads_q = load_graduates_annual_to_quarter(PATH_GRAD, q_union)

# 再把 CIER & GDP 对齐到并集索引，采用季末 last（已是季末）
cier_q  = cier_q.reindex(q_union)
gdp_q   = gdp_q.reindex(q_union)

# 合并
df_all = pd.concat([cier_q, grads_q, gdp_q], axis=1)
df_all = df_all.sort_index()

# 删除不需要的“第一产业、第二产业、第三产业”三列
df_all = df_all.drop(columns=['第一产业','第二产业','第三产业'])

# 只保留截止到 2022-12-31 的数据
df_all = df_all[df_all.index <= '2022-12-31']

# 导出
df_all.to_csv(OUT_PATH, encoding="utf-8-sig", float_format="%.6f")
print("✅ 已保存对齐后的季度数据：", OUT_PATH)
print("行数:", len(df_all))
if len(df_all) > 0:
    print("时间范围:", df_all.index.min().date(), "→", df_all.index.max().date())
